##parameters=form=None,returnRaw=False

# parameters: 
#   form:       The form containing search parameters
#   returnRaw:  Return the raw SQL Results instead of a sequence of assessment objects (Used for CSV export)

### DEPRECATED - This code is being rewritten in a more modular format, will be phased out.
  


from Products.KnowledgeEngine.AssessmentFilter import AssessmentFilter, decodeAssessmentFilterFromString
from Products.KnowledgeEngine.Utils.CallbackIterator import CallbackIterator
from Products.KnowledgeEngine.KERLProcessor import parseKERL


user = context.REQUEST.AUTHENTICATED_USER

# Do we want to limit to Managers only?
if not user.has_role('Manager'):
  raise "Access Denied"

if not form: form = context.REQUEST.form

assessmentStorageService = context.knowledgeengine_assessmentstorageservice
userProviderService = context.knowledgeengine_userproviderservice
keuser = userProviderService.getUser(context.REQUEST["AUTHENTICATED_USER"].getId())

# Check for a KERL statement passed from the form
# If KERL is passed, we use that to get a list of matching assessments, 
# completely overriding the SQL based search
kerl = form.get('kerl',None)
if kerl:
    
    # HACK - Pass all models through in our assessment filter
    models = filter(None,[b.getObject() for b in context.portal_catalog(portal_type='Model')])
    
    assessmentFilter = AssessmentFilter( "Demo Search", parseKERL(kerl), models=models )
    assessments = assessmentStorageService.getAssessmentsSatisfyingFilter( assessmentFilter, user )
    
    return assessments

mappings = context.demo_search_mapping()



# qstring is a function to `escape` a string to prevent SQL Injection attacks
qstring = assessmentStorageService.qstring

# Get the search.*:record values sent via the form
# i.e. If we pass search.name:record, then we can access it via search['name']
search = dict(form['search']) or {}

modelIdentities      = [qstring(identity) for identity in form.get('modelIdentities', [])]
repositoryIdentities = [qstring(identity) for identity in form.get('repositoryIdentities', [])]

# Get a list of repository names and convert to repository identities.
repositoryNames = form.get('repositoryNames', [])
for repositoryName in repositoryNames:
    
    # Get all repositories that match the given zope id
    repositories = context.getComponentsById( repositoryName, meta_type='Assessment Repository' )
    
    # Get the repository identities
    identities = [qstring(repo.getIdentity()) for repo in repositories]
    
    # Append to the repositoryIdentity list to filter on
    repositoryIdentities.extend( identities )



topsql = "SELECT DISTINCT search.* FROM search " # Create the start of our SQL Query
bottomsql = "WHERE 1 "

if repositoryIdentities:
    topsql += """
        INNER JOIN repositories 
            ON repositories.assessmentIdentity = search.assessmentIdentity AND repositories.modelIdentity = search.modelIdentity    
    """
    
    bottomsql += " AND repositories.repositoryIdentity IN ( %s )" % ','.join( repositoryIdentities )


""" 
    We now split up the search parameters into name/value pairs,
    loop over them, and build an SQL Query.
    
    i.e.
        <input name="search.name:record" value="bob"/>
        will come through as 
            key   = name
            value = bob
"""

joincount = 0 # Used to give each instance of the latestAnswers table a unique ID when we do an INNER JOIN

for mapping in mappings:
    
    searchIndex = mapping['idx']
    isMetadata  = mapping.get('isMetadata', False)
    
    searchValue = search.get(searchIndex, None)
    
    if not (searchIndex and searchValue): continue # Skip this searchIndex if no value is specified
    
    # All passed form data MUST be cleaned up before inserting into SQL To prevent an SQL Injection attack.
    #
    # We don't want to put quote characters (") around the searchIndex name, so pass quote=False to the qstring
    # function.
    
    searchType = mapping.get('searchType', 'equals')
    widget     = mapping.get('widget','auto')
    
    # Determine the exact SQL Clause to use for the search based on the mapping info.
    # (Getting ugly, needs to be done properly)
    
    if widget == 'MultiSelect':
        queryValue = 'IN (%s)' % ','.join( [qstring(value) for value in searchValue] )
        
    elif widget in ('Range','DateRange'):
        value_max = search.get( searchIndex + "_max", None )
        isInt     = mapping.get( 'valueType' ) == 'Integer'
        
        if value_max:
            queryValue = "BETWEEN %s and %s" % ( qstring(searchValue, quote=(not isInt)), qstring(value_max, quote=(not isInt)))
        else:
            queryValue = " = %s" % qstring(searchValue, quote=(not isInt))
        
        
    elif searchType == 'like':
        queryValue = "LIKE '%%%s%%'" % qstring(searchValue, quote=False)
    elif searchType == 'startswith':
        queryValue = "LIKE '%s%%'" % qstring(searchValue, quote=False)        
    elif searchType == 'fulltext':
        queryValue = qstring(searchValue)
    else:
        queryValue = "LIKE %s" % qstring(searchValue)


    if mapping.get('join', False): # Use the main KE tables rather than the searchIndex table.
                                     # Slower, but needed for multiselects at this stage.
        
        joinQueryValue = queryValue
        
        joinCond = []
        

        if mapping.get('link', False):
            
            linkSearchRelation, linkSearchIndex = mapping['link']
            
            joinQueryValue = """
                IN (SELECT assessmentIdentity 
                    FROM latestAnswers 
                    WHERE searchIndex=%s
                    AND   searchValue %s
                )
            """ % ( qstring(linkSearchIndex), queryValue )


        topsql += """
            INNER JOIN latestAnswers la%(joincount)s
            ON la%(joincount)s.assessmentIdentity = search.assessmentIdentity
            AND la%(joincount)s.searchIndex = '%(searchIndex)s'
            
            """ % {'joincount':joincount, 'searchIndex': searchIndex, 'queryValue': joinQueryValue}
        
        
        if mapping.get('sqlLink', False):
        
            linkTable, linkField, linkKey = mapping['sqlLink']
            
            topsql += """
                INNER JOIN %(linkTable)s as link%(joincount)s
                ON link%(joincount)s.%(linkKey)s = la%(joincount)s.searchValue
            """ % {'joincount': joincount, 'linkTable': linkTable, 'linkKey': linkKey}
            
            joinCond.append( " AND link%s.%s %s " % ( joincount, linkField, queryValue ) )
                
            #joincount += 1    

# -- AND la%(joincount)s.searchValue %(queryValue)s
        

        if not joinCond: 
            joinCond.append( " AND la%s.searchValue %s " % ( joincount, joinQueryValue ) )

        if modelIdentities:
          topsql += (" AND la%d.modelIdentity in (" % joincount) + ','.join(modelIdentities) + ") "
        
        bottomsql += "\n".join( joinCond )
        
        joincount += 1
        
        
        
        
    
    elif mapping.get('link', False):
        
        linkSearchRelation, linkSearchIndex = mapping['link']
                
        brain = dict( t_search           = "search%d" % joincount,
                      t_answers          = "la%s" % joincount,
                      
                      linkSearchIndex    = qstring( linkSearchIndex, quote=False ),
                      linkSearchRelation = qstring( linkSearchRelation ),
                      searchIndex        = qstring( searchIndex, quote=False ),
                      queryValue         = queryValue,
                      
        )
        
    
        topsql += """
            INNER JOIN search AS %(t_search)s 
                ON  %(t_search)s.searchRelation = %(linkSearchRelation)s
                AND %(t_search)s.assessmentIdentity = search.%(searchIndex)s
            
        """ % brain
        
        
        bottomsql += "AND %(t_search)s.%(linkSearchIndex)s %(queryValue)s" % brain
        
        
        joincount+=1    
    
    
    # 'sqlLink WITHOUT Join'
    elif mapping.get('sqlLink', False):
        
        linkTable, linkField, linkKey = mapping['sqlLink']
        topsql += """
            INNER JOIN %s as link%s
            ON link%s.%s = search.%s
        """ % ( linkTable, joincount, joincount, linkKey, searchIndex )
        
        bottomsql += " AND link%s.%s %s " % ( joincount, linkField, queryValue )
            
        joincount += 1    
        
    else:
    
        # Use python string manipulation to append 'AND searchIndex = "searchValue"' to the SQL Query.
        # We put spaces at the beginning and end so that multiple concatentations dont run into each other
        if isMetadata:
            
            # For metadata fields, we link to the 'assessments' table
            topsql    += """
                INNER JOIN assessments ON assessments.assessmentIdentity = search.assessmentIdentity
            """
            
            # And query directly against a particular field (i.e. createdBy)
            bottomsql +=  "AND assessments.%s %s" % (searchIndex, queryValue)
            
        else:
            if searchType == 'fulltext':
                bottomsql += "AND MATCH (search.%s) AGAINST (%s)" % (searchIndex, queryValue)
            else:
                bottomsql += " AND search.%s %s" % ( searchIndex, queryValue) 
            


sql = topsql + " " + bottomsql




if modelIdentities:
  sql += " AND search.modelIdentity in (" + ','.join(modelIdentities) + ") "

# 20070201 johnm    Commented out the line below as it screws up default searching. Uncomment
#                   as applicable.
#
#  sql += " ORDER BY KE4Search_choice, KE4Search_list_basic ASC " # You can sort on any search index.  
                                      # (ASC)ending is A-Z, use DESC to reverse the sort.                                      
                                      
## Generation an Exception containing the SQL Statement. Useful for debugging.                                      
#raise "<pre>%s</pre>" % sql                                        
                                      
# Send the query to the database, and return the results
results = assessmentStorageService.sqlQuery( sql )

# Did the caller request raw results? if so, return the base mysql resultset
if returnRaw:
    return results

# FIXME: We do an SQL Query and get a full resultset, then seperately load the assessment,
# doing the query again for each result- This is rather slow for large resultsets
# We need to push the already known results into an assessment object, and lazily query on other values.

loadAssessment = assessmentStorageService.loadAssessment

# Lazily load each assessment- Results view might use a pager, so we can save some time by not
# loading the full list of assessments unless they are needed.
return CallbackIterator( results, lambda result:loadAssessment(result['assessmentIdentity'] ));
